Problem Note 35555: Incorrect results might occur when you use the DISTINCT argument in the SQL procedure
 |  |  |  |
When you query a table from within PROC SQL, it is possible to get incorrect results. This problem can occur when the following conditions are true:
- A subquery is included in the WHERE clause to subset the data.
- The table in the query includes an index.
- Only the variable or variables that make up the index are included in the SELECT clause.
- The DISTINCT argument is included in the query.
The following example illustrates such a query:
data one(index=(test=(x y)));
input x y z;
cards;
1 1 1
1 1 2
1 3 3
2 1 4
;
run;
data two;
input z;
cards;
1
3
;
run;
proc sql;
create table test as
select distinct x,y
from one
where z in (select x from two);
quit;
You can use either of the following workarounds to circumvent this problem.
Workaround 1
This workaround involves processing the DISTINCT argument separate from the table subsetting, as shown here:
proc sql;
create table test as
select distinct *
from (select x,y
from one
where z in (select x from two));
quit;
Workaround 2
For this workaround, you need to store the values of the variable in a macro variable and then use the macro variable in the WHERE clause.proc sql noprint;
select z into :z_list separated by ','
from two;
create table test as
select distinct x,y
from one
where z in (&z_list);
quit;
Operating System and Release Information
| Product Family | Product | System | SAS Release | |
| Reported | Fixed* | |||
| SAS System | Base SAS | z/OS | 9.2 TS1M0 | 9.2 TS2M0 |
| Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.2 TS1M0 | 9.2 TS2M0 | ||
| Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.2 TS1M0 | 9.2 TS2M0 | ||
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.2 TS1M0 | 9.2 TS2M0 | ||
| Microsoft Windows XP 64-bit Edition | 9.2 TS1M0 | 9.2 TS2M0 | ||
| Microsoft® Windows® for x64 | 9.2 TS1M0 | 9.2 TS2M0 | ||
| Microsoft Windows Server 2003 Datacenter Edition | 9.2 TS1M0 | 9.2 TS2M0 | ||
| Microsoft Windows Server 2003 Enterprise Edition | 9.2 TS1M0 | 9.2 TS2M0 | ||
| Microsoft Windows Server 2003 Standard Edition | 9.2 TS1M0 | 9.2 TS2M0 | ||
| Microsoft Windows XP Professional | 9.2 TS1M0 | 9.2 TS2M0 | ||
| Windows Vista | 9.2 TS1M0 | 9.2 TS2M0 | ||
| 64-bit Enabled AIX | 9.2 TS1M0 | 9.2 TS2M0 | ||
| 64-bit Enabled HP-UX | 9.2 TS1M0 | 9.2 TS2M0 | ||
| 64-bit Enabled Solaris | 9.2 TS1M0 | 9.2 TS2M0 | ||
| HP-UX IPF | 9.2 TS1M0 | 9.2 TS2M0 | ||
| Linux | 9.2 TS1M0 | 9.2 TS2M0 | ||
| Linux for x64 | 9.2 TS1M0 | 9.2 TS2M0 | ||
| OpenVMS on HP Integrity | 9.2 TS1M0 | 9.2 TS2M0 | ||
| Solaris for x64 | 9.2 TS1M0 | 9.2 TS2M0 | ||
| Type: | Problem Note |
| Priority: | alert |
| Date Modified: | 2009-04-16 13:18:23 |
| Date Created: | 2009-04-14 15:12:56 |